unit pldb;

interface

uses SysUtils, Forms, Classes, TntGrids, Windows, TntSysUtils, ID3v2_DLL_Header,
     Graphics, TntGraphics, dll, ASGSQLite3, DB, TntSystem, header, Grids;

type
  TDataBase = class
  private
    SG: TTntStringGrid;
    MF: TForm;
    SQLDB: TASQLite3DB;
    SQLQ: TASQLite3Query;
    PlayList: TTrackList;
    TrackCount: Word;
    imps: TExp_struct;
    procedure BuilDB;
    procedure BuildGrid;
    procedure NumerateList(var SG: TTntStringGrid);
    function GetTagData(var Trck: TTrack): Boolean;
  public
    RetryCount: Word;
    constructor Create(MForm: TForm; StrG: TTntStringGrid);
    procedure SetTime(row: integer; time: integer);
    procedure CleanDB;
    procedure MakeDBList;
    procedure ParamDBList;
    procedure LoadDBList;
    procedure SaveDBList;
    procedure ClearDBList;
    procedure RemoveDBList(var index: Word; var nindex, pindex: Integer);
    procedure ClearGrid;
    procedure FitWidth;
    procedure ResizeGrid;
    procedure AddList(ListItem: WideString);
    procedure AddListEx(ListItem: TExp_struct_item);
    procedure ImportPL(FName: WideString);
    procedure SetNotExists(index: Word);
    procedure SetExists(index: Word);
    procedure Close;
    procedure Select(index: Word);
    function GetFileName(index: Word): WideString;
    function GetArtist(index: Word): WideString;
    function GetTrack(index: Word): WideString;
    function GetAlbum(index: Word): WideString;
    function GetSeconds(index: Word): Integer;
    function GetExists(index: Word): Boolean;
    function Row: Word;
    function ExportPL(FName: WideString; Extension: string):Boolean;
    function GetTrackCount: Word;
    function GetFormatedTrack(index: Word): WideString;
end;

implementation

constructor TDataBase.Create(MForm: TForm; StrG: TTntStringGrid);
begin
  inherited Create;

  //Ext_files := ['.mp3', '.ogg', '.wav'];
  //Ext_lists := ['.pls', '.m3u'];
  
  SG := StrG;
  MF := MForm;

  SQLDB := TASQLite3DB.Create(MForm);
  with SQLDB do
    begin
      Database := 'spdbase.db';
      CharacterEncoding := 'UTF8';
      MustExist := False;
      DefaultDir := ExtractFilePath(Application.ExeName);
      DriverDLL := 'sqlite3.dll';
      Open;
    end;

  SQLQ := TASQLite3Query.Create(MForm);
  with SQLQ do
    begin
      AutoCommit := False;
      SQLiteDateFormat := True;
      Connection := SQLDB;
      MaxResults := 0;
      StartResult := 0;
      TypeLess := False;
      SQLCursor := True;
      ReadOnly := False;
      UniDirectional := False;
      RawSQL := False;
    end;

  BuilDB;

  BuildGrid;
end;

function TDataBase.ExportPL(FName: WideString; Extension: string): Boolean;
var
  ES: TExp_struct;
  i: integer;
begin
  Result := False;

  SetLength(ES, TrackCount);

  //Prepare export structure
  for i := 0 to TrackCount - 1 do
    begin
      ES[i].fn := GetFileName(i);
      ES[i].tn := GetFormatedTrack(i);

      if Playlist[i].seconds > 0 then
        ES[i].tm := Playlist[i].seconds
      else
        ES[i].tm := -1;
    end;

  if Extension = 'txt' then
    Result := expTXT(PWideChar(FName), ES);

  if Extension = 'pls' then
    Result := expPLS(PWideChar(FName), ES);

  if Extension = 'm3u' then
    Result := expM3U(PWideChar(FName), ES);

  if Extension = 'html' then
    Result := expHTML(PWideChar(FName), ES);
end;

procedure TDataBase.FitWidth;
var
  i, w: integer;
begin
  w := 0;
  if TrackCount > 0 then
    for i := 0 to TrackCount - 1 do
      if WideCanvasTextWidth(SG.Canvas, SG.Cells[1, i]) > w then
        w := WideCanvasTextWidth(SG.Canvas, SG.Cells[1, i]);

  if w > 0 then
    MF.Width := 110 + w;
end;

function TDataBase.GetAlbum(index: Word): WideString;
begin
  Result := '';
  if index <= TrackCount - 1 then
    Result := PlayList[index].album;
end;

function TDataBase.GetArtist(index: Word): WideString;
begin
  Result := '';
  if index <= TrackCount - 1 then
    Result := PlayList[index].artist;
end;

function TDataBase.GetExists(index: Word): Boolean;
begin
  Result := False;
  if index <= TrackCount - 1 then
    Result := PlayList[index].fexists;
end;

function TDataBase.GetFileName(index: Word): WideString;
begin
  Result := '';
  if index <= TrackCount - 1 then
    Result := PlayList[index].filename;
end;

function TDataBase.GetFormatedTrack(index: Word): WideString;
begin
  Result := '';
  if index <= TrackCount - 1 then
    Result := SG.Cells[1, index];
end;

function TDataBase.GetSeconds(index: Word): Integer;
begin
  Result := -1;
  if index <= TrackCount - 1 then
    Result := PlayList[index].seconds;
end;

function TDataBase.GetTagData(var Trck: TTrack): Boolean;
var
  Tag: TTag;
begin
  Result := false;

  if ReadTag(PWideChar(Trck.filename), 1) then
    begin
      Tag.Title  := PWideChar(GetTag('Title'));
      Tag.Artist := PWideChar(GetTag('Artist'));
      Tag.Album  := PWideChar(GetTag('Album'));

      Trck.artist := Trim(Tag.Artist);
      Trck.track  := Trim(Tag.Title);
      Trck.album  := Trim(Tag.Album);

      Result := true;
    end;
end;

function TDataBase.GetTrack(index: Word): WideString;
begin
  Result := '';
  if index <= TrackCount - 1 then
    Result := PlayList[index].track;
end;

function TDataBase.GetTrackCount: Word;
begin
  Result := TrackCount;
end;

procedure TDataBase.ImportPL(FName: WideString);
var
  i: integer;
begin
  if Imp(PWideChar(FName), imps) then
    begin
      ClearDBList;
      ClearGrid;
      for i := 0 to Length(imps) - 1 do
        AddListEx(imps[i]);
    end;
end;

procedure TDataBase.BuilDB;
begin
  if not SQLDB.TableExists('playlist') then // TODO: check 'CREATE TABLE IF NOT EXISTS' - could be faster
    with SQLQ do
      begin
        StartTransaction;
        SQL.Text := 'CREATE TABLE playlist (id INTEGER, filename TEXT, artist TEXT, title TEXT, album TEXT, seconds INTEGER, fnupd INTEGER DEFAULT 0, PRIMARY KEY (id))';
        ExecSQL;
        Commit;
      end;
end;

procedure TDataBase.CleanDB;
begin
  with SQLQ do
    begin
      SQL.Text := 'VACUUM playlist';
      ExecSQL;
    end;
end;

procedure TDataBase.MakeDBList;
var
  i: integer;
begin
  with SQLQ do
    begin
      StartTransaction;
      SQL.Text := 'SELECT * FROM playlist';
      ExecSQL;
      Commit;

      for i := 0 to RecordCount - 1 do
        begin
          SetLength(PlayList, i + 1);

          PlayList[i].filename := UTF8Decode(FieldByName('filename').AsString);
          PlayList[i].artist   := UTF8Decode(FieldByName('artist').AsString);
          PlayList[i].track    := UTF8Decode(FieldByName('title').AsString);
          PlayList[i].album    := UTF8Decode(FieldByName('album').AsString);
          PlayList[i].seconds  := FieldByName('seconds').AsInteger;
          PlayList[i].updated  := (FieldByName('fnupd').AsInteger = 1);

          Next;
        end;
    end;

  TrackCount := length(PlayList);
end;

procedure TDataBase.ParamDBList;
var
  p: integer;
  ext: WideString;
begin
  if WideParamCount > 0 then
    if WideExtractFileExt(WideParamStr(1)) = '.pls' then
      ImportPL(WideParamStr(1))
    else
      for p := 1 to WideParamCount do
        begin
          ext := WideExtractFileExt(WideLowerCase(WideParamStr(p)));
          if (ext = '.mp3') or
             (ext = '.ogg') or
             (ext = '.wav') or
             (ext = '.mpc') or
             (ext = '.wma') or
             (ext = '.flac') or
             (ext = '.mod') or
             (ext = '.aac') or
             (ext = '.mp4') or
             (ext = '.m4a')
          then
            AddList(WideParamStr(p));
        end;
end;

procedure TDataBase.LoadDBList;
var
  i: integer;
  listentry: WideString;
  sel: Word;
begin
  sel := Row; //Remember selection

  SG.RowCount := 1;
  RetryCount := TrackCount;

  if TrackCount > 0 then
    begin
      SG.RowCount := TrackCount;
      for i := 0 to TrackCount - 1 do
        begin
          if not PlayList[i].updated then
            GetTagData(PlayList[i]);

          PlayList[i].updated := true;

          SetLength(listentry, 0);

          if (PlayList[i].artist = '') and (PlayList[i].track = '') then
            begin
              listentry := WideChangeFileExt(WideExtractFileName(PlayList[i].filename), '');
              if PlayList[i].album <> '' then
                listentry := listentry + ' ~ ' + PlayList[i].album;
            end;

          if (PlayList[i].artist = '') and (PlayList[i].track <> '') then
            begin
              listentry := PlayList[i].album;
              listentry := listentry + ' ~ ' + PlayList[i].track;
            end;

          if (PlayList[i].artist <> '') and (PlayList[i].track = '') then
            begin
              listentry := PlayList[i].artist;
              if PlayList[i].album <> '' then
                listentry := listentry + ' ~ ' + PlayList[i].album;
            end;

          if (PlayList[i].artist <> '') and (PlayList[i].track <> '') then
            begin
              listentry := PlayList[i].artist;
              if PlayList[i].album <> '' then
                listentry := listentry + ' ~ ' + PlayList[i].album;
              listentry := listentry + ' ~ ' + PlayList[i].track;
            end;

          SG.Cells[1,i] := listentry;

          if PlayList[i].seconds > 0 then
            SG.Cells[2,i] := PChar(SecToTime(PlayList[i].seconds))
          else
            SG.Cells[2,i] := '';

          PlayList[i].fexists := WideFileExists(PlayList[i].filename);
        end;

      NumerateList(SG);

      ResizeGrid;

      //Restore selection
      Select(sel); 

      //Make sure we see what we must :)
      SG.Refresh;
    end;
end;

procedure TDataBase.NumerateList(var SG: TTntStringGrid);
var
  i: Word;
begin
  if SG.RowCount > 0 then
    for i := 1 to SG.RowCount do
      SG.Cells[0, i - 1] := IntToStr(i) + '.';
end;

procedure TDataBase.SaveDBList;
var
  i: integer;
begin
  ClearDBList;

  with SQLQ do
    begin
      StartTransaction;
      for i := 0 to TrackCount - 1 do
        begin
          SQL.Text := 'INSERT INTO playlist (filename, artist, title, album, seconds, fnupd) values (:field1, :field2, :field3, :field4, :field5, :field6)';
          Params[0].AsBlob := UTF8Encode(PlayList[i].filename);
          Params[1].AsBlob := UTF8Encode(PlayList[i].artist);
          Params[2].AsBlob := UTF8Encode(PlayList[i].track);
          Params[3].AsBlob := UTF8Encode(PlayList[i].album);
          Params[4].AsInteger := Playlist[i].seconds;

          if Playlist[i].updated then
            Params[5].AsInteger := 1
          else
            Params[5].AsInteger := 0;

          ExecSQL;
        end;
      Commit;
    end;
end;

procedure TDataBase.BuildGrid;
begin
  with SG do
    begin
      ColWidths[0] := 25;
      ColWidths[1] := 100;
      ColWidths[2] := 30;
    end;
end;

procedure TDataBase.ClearDBList;
begin
  with SQLQ do
    begin
      StartTransaction;
      SQL.Text := 'DELETE FROM playlist';
      ExecSQL;
      Commit;
    end;
end;

procedure TDataBase.AddList(ListItem: WideString);
begin
  TrackCount := Length(PlayList) + 1;
  SetLength(PlayList, TrackCount);
  PlayList[TrackCount - 1].filename := ListItem;
end;

procedure TDataBase.AddListEx(ListItem: TExp_struct_item);
begin
  TrackCount := Length(PlayList) + 1;
  SetLength(PlayList, TrackCount);

  PlayList[TrackCount - 1].filename := ListItem.fn;

  if not WideSameStr(ListItem.tn, '') then
    PlayList[TrackCount - 1].listentry := ListItem.tn;

  if ListItem.tm > -1 then
    PlayList[TrackCount - 1].seconds := ListItem.tm;
end;

procedure TDataBase.ClearGrid;
begin
  SG.RowCount := 0;
  SetLength(PlayList, 0);
  TrackCount := 0;
end;

procedure TDataBase.Close;
begin
  SQLDB.Close;
end;

procedure TDataBase.Select(index: Word);
begin
  if index < TrackCount then
    SG.Row := index;
end;

procedure TDataBase.SetExists(index: Word);
begin
  if index < TrackCount then
    PlayList[index].fexists := True;
end;

procedure TDataBase.SetNotExists(index: Word);
begin
  if index < TrackCount then
    PlayList[index].fexists := False;
end;

procedure TDataBase.SetTime(row: integer; time: integer);
begin
  PlayList[row].seconds := time;
  SG.Cells[2, row] := PChar(SecToTime(time));
end;

procedure TDataBase.RemoveDBList(var index: Word; var nindex, pindex: Integer);
var
  j, i: Integer;
  st, sb, last, left: Word;
begin
  if TrackCount > 0 then
    begin
      st := SG.Selection.Top;
      sb := SG.Selection.Bottom;
      last := TrackCount - 1; //Last track in the list

      if sb = last then //Last track to remove is also the last in the list
        begin
          SetLength(PlayList, st);
          SG.RowCount := st;
          TrackCount := st;
        end
      else
        begin
          j := st;
          i := last - sb;

          while i > 0 do
            begin
              PlayList[j] := PlayList[sb - st + 1 + j];
              Dec(i);
              Inc(j);
            end;

          left := TrackCount - (sb - st + 1);

          SetLength(PlayList, left);
          SG.RowCount := left;
          TrackCount := left;
        end;

      //We are removing track that is currently playing
      if (index >= st) and (index <= sb) then
        begin
          //Set TrackIndex to something that is outside list, to avoid showing
          //active item, which in this case would be incorrect
          index := last + 1;

          nindex := st;

          //Are we removing a begining of the list?
          if st = 0 then
            //If yes, set PrevIndex to index of the last item in the list
            pindex := TrackCount - 1
          else
            //Otherwise, set it to first index before selection
            pindex := st - 1;
        end;

      LoadDBList;
    end;
end;

procedure TDataBase.ResizeGrid;
begin
  if Self <> nil then
    begin
      SG.ColWidths[1] := SG.Width - SG.ColWidths[0] - SG.ColWidths[2];

      if (GetWindowlong(SG.Handle, GWL_STYLE) and WS_VSCROLL) <> 0 then
        SG.ColWidths[1] := SG.ColWidths[1] - 16;
    end;
end;

function TDataBase.Row: Word;
begin
  Result := SG.Row;
end;

end.
